#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import os
import os.path
import re
import MySQLdb
import sys
import subprocess
from xgoogle.search import GoogleSearch, SearchError

dir = '/root/pool'
db = MySQLdb.connect(db = 'megabase', user = 'root', passwd = 'OhGheiw8EichahGh')
curAdd = db.cursor()

# Debouncage des adresses existantes : True/False
import_bounces = False

## dataplan : (regexp (string), target_category (int)
dataplan = [(".*", 5)]

#dataplan = [("^1-Hommes_Jeunes", 1),
#            ("^2-Hommes_Adultes", 2),
#            ("^3-Femmes_Jeunes", 3),
#            ("^4-Femmes_Adultes", 4)#,
#            #(".*", 0)
#            ]

for file in os.listdir(dir):
    source = open(os.path.join(dir, file))
    categorie = 0
    for element in dataplan:
        if re.search(element[0], file, re.I):
            categorie = element[1]
            print('\n---------------------------------------------\n')
            print('le fichier ' + file + ' correspond a la categorie ' + str(categorie))
            break
    count = 0
    if categorie != 0:
        lines_count = '???'
        OUT = subprocess.PIPE
        ERR = subprocess.PIPE
        process = subprocess.Popen('wc -l "' + os.path.join(dir, file) + '" | awk \'{print $1}\'', shell=True, stdout=OUT, stderr=ERR)
        output = process.communicate()[0]
        retcode = process.returncode
        if retcode == 0:
            lines_count = output.replace("\n", "")
        print(file + ' (' + str(lines_count) + ' lignes)')
        for line in source.readlines():
            count = count + 1
            if count == 1 or ( count > 9999 and str(count)[-4:] == '0000' ):
                if count != 1:
                    db.commit()
                print('traitement de la ligne ' + str(count) + '/' + str(lines_count))
            line = re.sub(",$", "", re.sub("\\r$", "", re.sub("\\n$", "", line)))
            curSearch = db.cursor()
            query = "SELECT `adresse`,`categorie` FROM `adresses` WHERE `adresse`='" + line + "'"
            if import_bounces == True:
                query = query + " AND `bounced`=1 AND `internet`=0"
            try:
                curSearch.execute(query)
                round = curSearch.fetchone()
            except:
                print('Impossible de traiter la ligne : ' + line)
                round = 'Error'
            if round != 'Error':
                if import_bounces == True:
                    if round != None:
                        curAdd.execute("UPDATE `adresses` SET `bounced`=0 WHERE `adresse`='" + line + "'")
                else:
                    if round != None:
                        if round[1] != str(categorie) and round[1] != '6':
                            curAdd.execute("UPDATE `adresses` SET `categorie`=" + str(categorie) + " WHERE `adresse`='" + line + "'")
                    else:
                        found = '0'
                        try:
                            gs = GoogleSearch(line)
                            gs.results_per_page = 5
                            results = gs.get_results()
                            for result in results:
                                if re.search(line, result.title, re.I) or re.search(line, result.desc, re.I):
                                    found = '1'
                                    break
                            curAdd.execute("INSERT INTO `adresses` (adresse, categorie, internet) VALUES ('" + line + "'," + str(categorie) + "," + found + ")")
                        except SearchError, e:
                            print("Search failed : %s" % e)
                            print("Current address : " + line)
            curSearch.close()
    source.close()
    db.commit()

curAdd.close()

sys.exit(0)